{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import re\n",
    "from pprint import pprint\n",
    "raw_df = pd.read_excel(\"20200424.xlsx\", sheet_name=\"4 甲基化建库表\")\n",
    "sampleId_unique_overlap = pd.read_excel(\"library.20200424.xlsx\", sheet_name=\"临床信息表(clinicalDf_overlap_libraryDf)\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_df.loc[:,\"华大样本编号P\"] = raw_df.loc[:,\"华大样本编号\"].str.slice_replace(2, 3, repl='P')\n",
    "raw_df.loc[:, \"虚拟入库样本名\"].fillna(method='ffill')\n",
    "raw_df.loc[:, \"pooling比例\"].fillna(method='ffill')\t\n",
    "raw_df.loc[:, \"取样ng\"].fillna(method='ffill')\t\n",
    "raw_df.loc[:, \"体积ul\"].fillna(method='ffill')\t\n",
    "raw_df.loc[:, \"杂交探针\"].fillna(method='ffill')\t\n",
    "raw_df.loc[:, \"杂交时间h\"].fillna(method='ffill')\t\n",
    "raw_df.loc[:, \"PostPCR循环数\"].fillna(method='ffill')\t\n",
    "raw_df.loc[:, \"PostPCR浓度ng/ul\"].fillna(method='ffill')\t\n",
    "raw_df.loc[:, \"洗脱体积\"].fillna(method='ffill')\t\n",
    "raw_df.loc[:, \"文库质控\"].fillna(method='ffill')\t\n",
    "raw_df.loc[:, \"文库名称\"] = raw_df.loc[:, \"文库名称\"].fillna(method='ffill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_df_filt = raw_df.loc[raw_df.loc[:,\"虚拟入库样本名\"]!=\"-\",]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "shape of raw_df: (7061, 30); shape of raw_df_filt: (3314, 30); shape of sampleId_unique_overlap: (1105, 3); \n"
    }
   ],
   "source": [
    "raw_df_filt = raw_df.loc[raw_df[\"样本编号\"].isin(sampleId_unique_overlap.loc[:, \"华大样本编号\"])]\n",
    "# raw_df_filt = raw_df_filt.loc[raw_df_filt.loc[:,\"子文库编号\"].str.contains(\"P-\"),]\n",
    "# raw_df_filt.drop_duplicates(\"样本编号\", \"first\", inplace=True)\n",
    "# raw_df_filt = raw_df_filt.dropna(subset=[\"上机日期\"]).drop_duplicates(\"样本编号\", \"first\")\n",
    "print(\"shape of raw_df: {}; shape of raw_df_filt: {}; shape of sampleId_unique_overlap: {}; \".format(raw_df.shape, raw_df_filt.shape, sampleId_unique_overlap.shape))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "<bound method NDFrame.head of             样本编号        原样编号        样本类型        样本名称 DNA浓度（ng/μL） DNA总量（ng）  \\\n324   18P4063779       P2100      北大肺癌血浆       P2100         0.76     31.92   \n369   18P4063896       P2315      北大肺癌血浆       P2315         0.56     23.52   \n377   18P4063753       P2029      北大肺癌血浆       P2029          NaN       NaN   \n378   18P4063860       P2036      北大肺癌血浆       P2036          NaN       NaN   \n380   18P4063857       P1828      北大肺癌血浆       P1828          NaN       NaN   \n...          ...         ...         ...         ...          ...       ...   \n6971  19P7247863  19P7247863  中大六院肠癌血浆样本  19P7247863          NaN       NaN   \n6972  19P7247850  19P7247850  中大六院肠癌血浆样本  19P7247850          NaN       NaN   \n6973  19P7247862  19P7247862  中大六院肠癌血浆样本  19P7247862          NaN       NaN   \n6974  19P7247867  19P7247867  中大六院肠癌血浆样本  19P7247867          NaN       NaN   \n6975  19P7247884  19P7247884  中大六院肠癌血浆样本  19P7247884          NaN       NaN   \n\n     input量(ng)  建库方法          子文库编号 出库浓度(ng/μL)  ...        送测日期 捕获文库备注  \\\n324          全部  T4进口  18P4063779S-1        38.4  ...         NaN    NaN   \n369       23.52  T4进口  18P4063896S-1        31.6  ...         NaN    NaN   \n377         NaN  T4进口  18P4063753S-1         NaN  ...         NaN    NaN   \n378         NaN  T4进口  18P4063860S-1         NaN  ...         NaN    NaN   \n380         NaN  T4进口  18P4063857S-1         NaN  ...         NaN    NaN   \n...         ...   ...            ...         ...  ...         ...    ...   \n6971        NaN  单链建库  19P7247863M-1         NaN  ...  20200204.0    TBS   \n6972        NaN  单链建库  19P7247850M-1         NaN  ...  20200204.0    TBS   \n6973        NaN  单链建库  19P7247862M-1         NaN  ...  20200204.0    TBS   \n6974        NaN  单链建库  19P7247867M-1         NaN  ...  20200204.0    TBS   \n6975        NaN  单链建库  19P7247884M-1         NaN  ...  20200204.0    TBS   \n\n                     上机日期                 下机日期            机器号  \\\n324   2018-10-29 18:00:00  2018-11-01 09:52:00  R100400180026   \n369   2018-11-13 17:25:00  2018-11-16 11:25:00  R100400180038   \n377   2018-11-13 17:25:00  2018-11-16 11:25:00  R100400180038   \n378   2018-11-13 17:25:00  2018-11-16 11:25:00  R100400180038   \n380   2018-11-13 17:25:00  2018-11-16 11:25:00  R100400180038   \n...                   ...                  ...            ...   \n6971  2020-02-05 15:48:00  2020-02-07 15:30:00  R100400180025   \n6972  2020-02-05 15:48:00  2020-02-07 15:30:00  R100400180025   \n6973  2020-02-05 15:48:00  2020-02-07 15:30:00  R100400180025   \n6974  2020-02-05 15:48:00  2020-02-07 15:30:00  R100400180025   \n6975  2020-02-05 15:48:00  2020-02-07 15:30:00  R100400180025   \n\n                        芯片号   Lane                      数据地址   备注  组合信息  \n324   V100008545，V100008561  L1-L4  /ifs9/zebra/MGISEQ-2000/  NaN   NaN  \n369              V300007495  L1-L4  /ifs9/zebra/MGISEQ-2000/  NaN   NaN  \n377              V300007664  L1-L4  /ifs9/zebra/MGISEQ-2000/  NaN   NaN  \n378              V300007664  L1-L4  /ifs9/zebra/MGISEQ-2000/  NaN   NaN  \n380              V300007664  L1-L4  /ifs9/zebra/MGISEQ-2000/  NaN   NaN  \n...                     ...    ...                       ...  ...   ...  \n6971  V300044901 V300044737  L1-L8  /ifs9/zebra/MGISEQ-2000/  NaN   NaN  \n6972  V300044901 V300044737  L1-L8  /ifs9/zebra/MGISEQ-2000/  NaN   NaN  \n6973  V300044901 V300044737  L1-L8  /ifs9/zebra/MGISEQ-2000/  NaN   NaN  \n6974  V300044901 V300044737  L1-L8  /ifs9/zebra/MGISEQ-2000/  NaN   NaN  \n6975  V300044901 V300044737  L1-L8  /ifs9/zebra/MGISEQ-2000/  NaN   NaN  \n\n[1000 rows x 29 columns]>"
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "source": [
    "raw_df_filt.head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "19      LS278\n20      LS278\n52      LM252\n53      LM252\n54      LM252\n        ...  \n2343    LP051\n2352    LP051\n2354    LP051\n2356    LP051\n2382    LP052\nName: 0, Length: 385, dtype: object"
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "source": [
    "capture_df = pd.DataFrame({\n",
    "    \"捕获文库名\": raw_df_filt.loc[:, \"Pooling号\"].str.extract(r'(\\w+)[(（]?', flags=re.IGNORECASE, expand=True).iloc[:,0],\n",
    "    \"杂交日期\": [np.nan for x in raw_df_filt.index],\n",
    "    \"杂交探针\": ,\n",
    "    \"杂交时间(min)\": ,\n",
    "    \"PostPCR循环数\": ,\n",
    "    \"PostPCR浓度\": ,\n",
    "    \"洗脱体积\": ,\n",
    "    \"操作人\": [np.nan for x in raw_df_filt.index],\n",
    "    \"备注\": [np.nan for x in raw_df_filt.index]\n",
    "})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "P    2494\nn       1\n        1\ndtype: int64"
     },
     "metadata": {},
     "execution_count": 3
    }
   ],
   "source": [
    "label=pd.Series([ str(x)[2:3] for x in raw_df.loc[:, \"样本编号P\"]])\n",
    "label.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python36764bit590e0209625c41dab6b137cee9f052d1",
   "display_name": "Python 3.6.7 64-bit"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}